﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using SQLServerHealthCheck.SQLServer;

namespace SQLServerHealthCheck.PL
{
    public partial class Trace_ServLookup : Form
    {
        public static string strselectedServerTrace = null;

        public Trace_ServLookup()
        {
            InitializeComponent();
        }

        private void TraceLookup_Load(object sender, EventArgs e)
        {
            LoadGridView();
        }

        private void LoadGridView()
        {
            //selected SQLInstance in HealthCheckControl Form

            if (CollectServerAndServiceData.IsSQLServerAccessible(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringTemplate"].ToString()))
            {


                string strselectedSQLInstance = HealthCheckControlPanel.strselectedSQLInstance.ToString();

                SqlConnection cnHealthCheck = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringTemplate"]);
                cnHealthCheck.Open();

                DataTable dt = new DataTable();

                SqlCommand ocmd = new SqlCommand();
                ocmd.CommandType = CommandType.Text;
                ocmd.CommandText = "select ServerTraceId,ServerName,DatabaseName,TraceStartDate,TraceEndDate,TraceStartTime,TraceEndTime from Trace_Server where ServerName='" + strselectedSQLInstance + "' order by ServerTraceId desc ";
                ocmd.Connection = cnHealthCheck;

                SqlDataAdapter sqldadap = new SqlDataAdapter(ocmd);
                sqldadap.Fill(dt);

                dGViewTraceDetas.DataSource = dt;


                dGViewTraceDetas.Columns[1].Visible = false;
                dGViewTraceDetas.Columns[2].Visible = false;

                for (int i = 1; i < dGViewTraceDetas.Columns.Count; i++)
                {
                    dGViewTraceDetas.Columns[i].ReadOnly = true;
                }


            }
            else
            {
                MessageBox.Show("The SQLServerHealthCheck database to store health check details is inaccessible.Please check if database server is up or check the config file to see a valid Server name has been entered.", "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Information);

            }

        }

        private void cmdImportTrace_Click(object sender, EventArgs e)
        {

            string selectedIndex = "";

            foreach (DataGridViewRow dr in dGViewTraceDetas.Rows)
            {

                if (dr.Cells[0].Value != null)
                {
                    if (selectedIndex.Trim().Length == 0)
                    {
                        selectedIndex = dr.Cells[1].Value.ToString();
                    }
                    else
                    {
                        selectedIndex = selectedIndex + "," + dr.Cells[1].Value.ToString();
                    }
                }

            }

            if (selectedIndex.Trim().Length == 0)
            {
                MessageBox.Show("Please select a trace file to import", "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }

                //import the trace files
            else
            {
                SQLTrace.ImportTraceDetailsfromServer(selectedIndex.Split(','));
                this.Close();

            }



        }

        private void cmdServerShowReport_Click(object sender, EventArgs e)
        {
            string selectedIndex = "";

            foreach (DataGridViewRow dr in dGViewTraceDetas.Rows)
            {


                if (dr.Cells[1].Value != null)
                {

                    if ((bool)(dr.Cells[0].FormattedValue))
                    {
                        if (selectedIndex.Trim().Length == 0)
                        {
                            selectedIndex = dr.Cells[1].Value.ToString();
                        }
                        else
                        {
                            selectedIndex = selectedIndex + "," + dr.Cells[1].Value.ToString();
                        }
                    }
                }

            }

            if (selectedIndex.Trim().Length == 0)
            {
                MessageBox.Show("Please select a trace file to show the  report", "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }


            string[] strselectedindex = selectedIndex.Split(',');

            if (strselectedindex.Length > 1)
            {
                MessageBox.Show("Please select one trace file", "SQLServerHealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            else
            {
                this.Cursor = Cursors.WaitCursor;

                strselectedServerTrace = strselectedindex[0]; //sets the selected ServerTraceId
                ShowReportwithNormalizedText("dbo.TraceDetails_Server", "ServerTraceId");



                TraceViewReport frm = new TraceViewReport();
                //strselectedServerTrace = strselectedindex[0];
                frm.MdiParent = this.MdiParent;
                frm.Show();
                this.Cursor = Cursors.Default;


            }
        }

        public void ShowReportwithNormalizedText(string strTableName, string strColName)
        {
            string strSqlText = "";
            string strNormalizedText = "";

            SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringTemplate"].ToString());
            conn.Open();

            String strsql = "delete from  TraceDetails_NormalizedText ";
            SqlCommand comm = new SqlCommand(strsql, conn);

            comm.ExecuteNonQuery();

            DataSet ds = new DataSet();
            SqlDataAdapter adap = new SqlDataAdapter("select " + strColName + " ,EventClass,TextData,SPID,CPU,Reads,Writes,Duration  from  " + strTableName + " where EventClass in(10,12) and  " + strColName + "=" + strselectedServerTrace, conn);
            adap.Fill(ds);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                if (dr[2].ToString().Trim().Length != 0)
                {
                    strNormalizedText = SQLTrace.NormalizeSqlText(dr[2].ToString(), dr[1].ToString(), Convert.ToInt32(dr[3].ToString()));
                    strSqlText = dr[2].ToString();

                    //there is a small glitch here. I need to replace single quotes with double quotes here 
                    //actually this hould go here .Replace("'", "\"");
                    //strNormalizedText=strNormalizedText.Replace('\'',' ');
                    //strSqlText = strSqlText.Replace('\'', ' ');
                    strNormalizedText = strNormalizedText.Replace("'", "\"");
                    strSqlText = strSqlText.Replace("'", "\"");
                }


                strsql = "insert into  TraceDetails_NormalizedText(ServerTraceId,TextData,NormalizedTextData,CPU,Reads,Writes,Duration) values(" + Convert.ToInt64(dr[0].ToString()) + ",'" + strSqlText + "','" + strNormalizedText + "'," + Convert.ToInt32(dr[4].ToString()) + "," + Convert.ToInt32(dr[5].ToString()) + "," + Convert.ToInt32(dr[6].ToString()) + "," + Convert.ToInt32(dr[7].ToString()) + ")";
                SqlCommand cmd = new SqlCommand(strsql, conn);
                cmd.ExecuteNonQuery();

            }
        }


    }
}
